import mysql.connector
from mysql.connector import Error
import time
from datetime import datetime

# 数据库连接配置
config = {
    'user': 'root',
    'password': '200206',
    'host': 'localhost',
    'database': 'Temperature_Control_System',
    'raise_on_warnings': True
}

def is_valid_temperature(temp):
    return temp is not None and isinstance(temp, (int, float))

def verify_temperature(new_temp, prev_temp):
    if prev_temp is None or not is_valid_temperature(new_temp):
        return True
    return abs(new_temp - prev_temp) <= 5

def store_cleaned_data(temp, log_time, cursor, cnx):
    print(f"存储清洗后的数据：温度={temp}, 时间={log_time}")
    insert_query = "INSERT INTO cleaned_temperature_log (temperature, log_time) VALUES (%s, %s)"
    try:
        if isinstance(log_time, datetime):
            log_time = log_time.strftime('%Y-%m-%d %H:%M:%S')
        cursor.execute(insert_query, (temp, log_time))
        cnx.commit()
        print("数据成功存储到cleaned_temperature_log表")
    except Error as e:
        print(f"存储数据时发生错误：{e}")
        cnx.rollback()

def main():
    prev_temp = None
    print("程序开始运行")
    try:
        cnx = mysql.connector.connect(**config)
        cursor = cnx.cursor()
        while True:
            print("开始查询未处理数据...")
            # 确保查询之前提交任何未处理的事务
            cnx.commit()
            query = "SELECT Temp_id, temperature, log_time FROM temperature_log WHERE processed = FALSE ORDER BY log_time ASC"
            cursor.execute(query)
            unprocessed_data = cursor.fetchall()
            print(unprocessed_data)
            print(f"查询到的未处理数据条数: {len(unprocessed_data)}")
            if unprocessed_data:
                for record_id, new_temp, new_time in unprocessed_data:
                    if verify_temperature(new_temp, prev_temp):
                        store_cleaned_data(new_temp, new_time, cursor, cnx)
                        prev_temp = new_temp
                    else:
                        print("验证失败，记录被忽略")
                # 更新已处理的记录
                temp_ids = [id for id, _, _ in unprocessed_data]
                if temp_ids:
                    placeholders = ', '.join(['%s'] * len(temp_ids))
                    time.sleep(1)
                    update_query = f"UPDATE temperature_log SET processed = TRUE WHERE Temp_id IN ({placeholders})"
                    cursor.execute(update_query, temp_ids)
                    cnx.commit()
                    print("标记已处理的记录")
            else:
                print("没有查询到新的未处理数据")
            time.sleep(1)
            print("等待下一次查询...\n")
    except KeyboardInterrupt:
        print("程序被用户中断")
        if cnx.is_connected():
            cnx.close()
    except Error as e:
        print(f"连接到MySQL时发生错误：{e}")
    finally:
        if cnx.is_connected():
            cnx.close()
        print("数据库连接已关闭")

if __name__ == "__main__":
    main()